-- @owner:yangjian
-- @date:2024-4-2
-- @testpoint:1.去除大对象使用限制，存储过程中使用大对象测试，合理报错

--清理环境
CREATE OR REPLACE FUNCTION f_opengauss_large_object_pl_case0003_1()  
RETURNS VOID AS 
$$
BEGIN  
    BEGIN
    PERFORM lo_unlink(100);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
    BEGIN
    PERFORM lo_unlink(101);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
    BEGIN
    PERFORM lo_unlink(200);
    EXCEPTION
        WHEN others THEN
            NULL;
    end;
END;  
$$
LANGUAGE plpgsql;
/

select f_opengauss_large_object_pl_case0003_1();

--环境准备
drop table if exists t_opengauss_large_object_pl_case0003_1 cascade;
CREATE TABLE t_opengauss_large_object_pl_case0003_1 (loid oid,fd int);

--step1:对lo_creat使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_1() is
begin
PERFORM lo_creat(101);
end;
/
call pro_opengauss_large_object_pl_case0003_1();

--step2:对lo_create使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_2() is
begin
PERFORM lo_create(100);
end;
/
call pro_opengauss_large_object_pl_case0003_2();

--step3:对lo_open,loread,lowrite,lo_close使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_3() is
begin
PERFORM lo_open(100,CAST(x'20000' | x'40000' AS integer));
PERFORM lowrite (0,'aaaaaaaaaa');
PERFORM loread (0, 6);
PERFORM lo_close (0);
end;
/
call pro_opengauss_large_object_pl_case0003_3();

--step4:对lo_seek,lo_seek64使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_4() is
begin
PERFORM lo_open(100,CAST(x'20000' | x'40000' AS integer));
PERFORM lo_lseek(0,5,0);
PERFORM lo_lseek64(0,5,0);
PERFORM lo_close (0);
end;
/
call pro_opengauss_large_object_pl_case0003_4();

--step5:对lo_tell,lo_tell64使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_5() is
begin
PERFORM lo_open(100,CAST(x'20000' | x'40000' AS integer));
PERFORM lo_tell(0);
PERFORM lo_tell64(0);
PERFORM lo_close (0);
end;
/
call pro_opengauss_large_object_pl_case0003_5();

--step6:对lo_truncate,lo_truncate64使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_6() is
begin
PERFORM lo_open(100,CAST(x'20000' | x'40000' AS integer));
PERFORM lowrite (0,'aaaaaaaaaa');
PERFORM lo_truncate(0,2);
PERFORM lo_truncate64(0,1);
PERFORM lo_close (0);
end;
/
call pro_opengauss_large_object_pl_case0003_6();

--step7:对lo_put,lo_get使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_7() is
begin
PERFORM lo_put(100,6,'aaaaaa');
PERFORM lo_get(100,6,6);
end;
/
call pro_opengauss_large_object_pl_case0003_7();

--step8:对lo_from_bytea使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_8() is
begin
PERFORM lo_from_bytea(200,'aaaaaa');
end;
/
call pro_opengauss_large_object_pl_case0003_8();

--step9:对lo_unlink使用;expect:成功
create or replace procedure pro_opengauss_large_object_pl_case0003_9() is
begin
PERFORM lo_unlink(100);
end;
/
call pro_opengauss_large_object_pl_case0003_9();

--清理环境
select lo_unlink(loid) from pg_largeobject;
select f_opengauss_large_object_pl_case0003_1();
drop table if exists t_opengauss_large_object_pl_case0003_1 cascade;
drop function f_opengauss_large_object_pl_case0003_1();
drop procedure pro_opengauss_large_object_pl_case0003_1;
drop procedure pro_opengauss_large_object_pl_case0003_2;
drop procedure pro_opengauss_large_object_pl_case0003_3;
drop procedure pro_opengauss_large_object_pl_case0003_4;
drop procedure pro_opengauss_large_object_pl_case0003_5;
drop procedure pro_opengauss_large_object_pl_case0003_6;
drop procedure pro_opengauss_large_object_pl_case0003_7;
drop procedure pro_opengauss_large_object_pl_case0003_8;
drop procedure pro_opengauss_large_object_pl_case0003_9;

